﻿using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Libraries.Common;
using Libraries.Entities;

namespace Libraries.Repositories
{
    public class LanguageScoreRepository
    {
        private static LanguageScore ParseToEntityLanguageScore(IDataRecord dataReader)
        {
            if (dataReader != null)
            {
                return new LanguageScore
                {
                    EmployeeId = dataReader["EmployeeId"].ToStringOrDefault(),
                    LanguageTypeId = dataReader["LanguageTypeId"].ToInt32OrDefault(),
                    Score = dataReader["Score"].ToStringOrDefault(),
                    DepartmentId = dataReader["DepartmentId"].ToInt32OrDefault(),
                    LanguageScoreId = dataReader["LanguageScoreId"].ToInt32OrDefault(),
                    DepartmentName = dataReader["DepartmentName"].ToStringOrDefault(),
                    LanguageName = dataReader["LanguageName"].ToStringOrDefault(),
                    Note = dataReader["Note"].ToStringOrDefault(),
                    Date = dataReader["Date"].ToDateTimeOrNull(),
                    CreatedBy = dataReader["CreatedBy"].ToInt32OrDefault(),
                    CreatedDate = dataReader["CreateDate"].ToDateTimeOrNull(),
                    FullName = dataReader["FullName"].ToStringOrDefault(),
                    ModifiedBy = dataReader["ModifiedBy"].ToInt32OrDefault(),
                    ModifiedDate = dataReader["ModifiedDate"].ToDateTimeOrNull(),
                    AccountName = dataReader["AccountName"].ToStringOrDefault(),
                    TotalRow = dataReader["TotalRow"].ToInt32OrDefault()
                };
            }
            return null;
        }

         public Collection<LanguageScore> SearchLanguageScorePaging(int pageSize, int currentPage, string accountName, string fullName, int? languageTypeId, string score, int departmentId, int statusAccountId, bool final)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@CurrentPage",currentPage),
                                                          new SqlParameter("@AccountName",string.IsNullOrEmpty(accountName)? DBNull.Value : (object)accountName),
                                                          new SqlParameter("@FullName",string.IsNullOrEmpty(fullName)?DBNull.Value:(object)fullName),
                                                          new SqlParameter("@LanguageTypeId",languageTypeId == 0?DBNull.Value:(object)languageTypeId),
                                                          new SqlParameter("@Score",string.IsNullOrEmpty(score)?DBNull.Value:(object)score),
                                                          new SqlParameter("@DepartmentId",departmentId == 0?DBNull.Value:(object)departmentId),
                                                          new SqlParameter("@StatusAccountId",statusAccountId == 0?DBNull.Value:(object)statusAccountId),
                                                          new SqlParameter("@Final",final?1:0)
                                                      };
            Collection<LanguageScore> list = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.PagingLanguageScore))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.PagingLanguageScore, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        list = new Collection<LanguageScore>();
                        while (result.Read())
                        {
                            list.Add(ParseToEntityLanguageScore(result));
                        }
                    }
                }
            }
            return list;
        }

        public Collection<LanguageScore> ListLanguageScoresHistory(string accountName)
        {
            var parameters = new Collection<SqlParameter>
                                 {
                                     new SqlParameter("@AccountName",accountName)
                                 };
            Collection<LanguageScore> list = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.LanguageScoreHistory))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.LanguageScoreHistory, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        list = new Collection<LanguageScore>();
                        while (result.Read())
                        {
                            list.Add(new LanguageScore
                                         {
                                             Score = result["Score"].ToStringOrDefault(),
                                             LanguageName = result["LanguageName"].ToStringOrDefault(),
                                             Note = result["Note"].ToStringOrDefault(),
                                             Date = result["Date"].ToDateTimeOrDefault(),
                                             AccountName = result["AccountName"].ToStringOrDefault(),
                                             //QuyenNTH extension
                                             LanguageTypeId = result["LanguageTypeId"].ToInt32OrDefault()
                                         });
                        }
                    }
                }
            }
            return list;
        }

        public LanguageScore GetLanguageScoreDetailById(int languageScoreId)
        {
            var parameters = new Collection<SqlParameter>
                                 {
                                     new SqlParameter("@LanguageScoreId",languageScoreId)
                                 };
            LanguageScore languageScore = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetLanguageScoreById))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetLanguageScoreById, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        if (result.Read())
                        {
                            languageScore = new LanguageScore
                                                {
                                                    EmployeeId = result["EmployeeId"].ToStringOrDefault(),
                                                    LanguageTypeId = result["LanguageTypeId"].ToInt32OrDefault(),
                                                    Score = result["Score"].ToStringOrDefault(),
                                                    DepartmentId = result["DepartmentId"].ToInt32OrDefault(),
                                                    LanguageScoreId = result["LanguageScoreId"].ToInt32OrDefault(),
                                                    DepartmentName = result["DepartmentName"].ToStringOrDefault(),
                                                    LanguageName = result["LanguageName"].ToStringOrDefault(),
                                                    Note = result["Note"].ToStringOrDefault(),
                                                    Date = result["Date"].ToDateTimeOrDefault(),
                                                    CreatedBy = result["CreatedBy"].ToInt32OrDefault(),
                                                    CreatedDate = result["CreateDate"].ToDateTimeOrDefault(),
                                                    FullName = result["FullName"].ToStringOrDefault(),
                                                    ModifiedBy = result["ModifiedBy"].ToInt32OrDefault(),
                                                    ModifiedDate = result["ModifiedDate"].ToDateTimeOrDefault(),
                                                    AccountName = result["AccountName"].ToStringOrDefault()
                                                };
                        }
                    }
                }
            }
            return languageScore;
        }

        public Collection<LanguageScore> ListLanguageScoreByLanguageType(int pageSize, int currentPage,int? languageTypeId)
        {
            var parameters = new Collection<SqlParameter>
                                                      {
                                                          new SqlParameter("@PageSize",pageSize),
                                                          new SqlParameter("@CurrentPage",currentPage),
                                                          new SqlParameter("@AccountName",DBNull.Value),
                                                          new SqlParameter("@FullName",DBNull.Value),
                                                          new SqlParameter("@LanguageTypeId",languageTypeId == 0?DBNull.Value:(object)languageTypeId),
                                                          new SqlParameter("@Score",DBNull.Value),
                                                          new SqlParameter("@DepartmentId",DBNull.Value),
                                                          new SqlParameter("@StatusAccountId",DBNull.Value),
                                                          new SqlParameter("@Final",true)
                                                      };
            Collection<LanguageScore> list = null;
            if (!string.IsNullOrEmpty(ProcedureConfiguration.PagingLanguageScore))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.PagingLanguageScore, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        list = new Collection<LanguageScore>();
                        while (result.Read())
                        {
                            list.Add(ParseToEntityLanguageScore(result));
                        }
                    }
                }
            }
            return list;
        }

        public Collection<LanguageScore> GetLanguageScoreHistoryByEmployeeId(string employeeId)
        {
            var parameters = new Collection<SqlParameter>
                                 {
                                     new SqlParameter("@EmployeeId",employeeId)
                                 };
            var scores = new Collection<LanguageScore>();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetLanguageScoreHistoryByEmployeeId))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetLanguageScoreHistoryByEmployeeId, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        while (result.Read()) {
                            scores.Add(new LanguageScore {
                                                             EmployeeId = result["EmployeeId"].ToStringOrDefault(),
                                                             LanguageTypeId = result["LanguageTypeId"].ToInt32OrDefault(),
                                                             Score = result["Score"].ToStringOrDefault(),
                                                             LanguageScoreId = result["LanguageScoreId"].ToInt32OrDefault(),
                                                             LanguageName = result["LanguageName"].ToStringOrDefault(),
                                                             Note = result["Note"].ToStringOrDefault(),
                                                             Date = result["Date"].ToDateTimeOrNull(),
                                                             AccountName = result["AccountName"].ToStringOrDefault()
                                                         });
                        }
                    }
                }
            }
            return scores;
        }

        public Collection<LanguageScore> HistoryLanguageScoreByTypeAndEmployeeId(int languageType, string employeeId) {
            var parameters = new Collection<SqlParameter> {
                new SqlParameter("@LanguageTypeId",languageType),
                new SqlParameter("@EmployeeId",employeeId)
                                                          };
            var scores = new Collection<LanguageScore>();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.HistoryLanguageScoreByTypeAndEmployeeId))
            {
                using (var result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.HistoryLanguageScoreByTypeAndEmployeeId, parameters, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        while (result.Read())
                        {
                            scores.Add(new LanguageScore
                            {
                                Score = result["Score"].ToStringOrDefault(),
                                LanguageName = result["LanguageName"].ToStringOrDefault(),
                                Note = result["Note"].ToStringOrDefault(),
                                Date = result["Date"].ToDateTimeOrNull(),
                                AccountName = result["AccountName"].ToStringOrDefault()
                            });
                        }
                    }
                }
            }
            return scores;
        } 
    }
}
